package org.apselex.providers;

/*
 * Copyright (c) 2008 Evan Williams
 * 
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 * 
 * The above copyright notice and this permission notice shall be included in
 * all copies or substantial portions of the Software.
 * 
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
 * SOFTWARE.
 */

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;

import org.apache.log4j.Logger;
import org.apselex.model.ModelElement;
import org.apselex.model.ModelMetaData;
import org.apselex.model.ModelTag;

import apselmodel.Activator;


public class DataProvider {

	private String sDatabaseName = null;

	private Connection conn = null;

	private static Logger logger = Logger.getLogger(DataProvider.class);

	/**
	 * @param databaseName
	 */
	public DataProvider() {
		sDatabaseName = null;
		init();
	}

	/**
	 * @param databaseName
	 */
	public DataProvider(String databaseName) {
		sDatabaseName = databaseName;
		init();
	}

	public String getDatabaseName() {
		return sDatabaseName;
	}

	public void setDatabaseName(String databaseName) {
		sDatabaseName = databaseName;
		logger.debug("setting database name to:" + sDatabaseName);
		try {
			conn = DriverManager.getConnection("jdbc:sqlite:" + sDatabaseName);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void init() {

		String sURL = "jdbc:odbc:psa";

		try {

			Class.forName("org.sqlite.JDBC");
			conn = null;
//			conn = DriverManager.getConnection("jdbc:sqlite:c:/catalog.psedb");

//		} catch (SQLException e) {
//			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public Object[] getElements() {

		Object[] r = null;

		if (conn == null) {
			return r;
		}
		ArrayList<ModelElement> a = new ArrayList<ModelElement>();
		try {

//			DatabaseMetaData dbmd = conn.getMetaData();
//			ResultSet rstab = dbmd.getTables(null, null, null, null);
//			while (rstab.next()) {
//				String sSchema = rstab.getString("TABLE_SCHEM");
//				String sTable = rstab.getString("TABLE_NAME");
//
//				logger.debug("name   = " + sSchema);
//				logger.debug("schema = " + sTable);
//
//				printCols(dbmd, sSchema, sTable);
//
//			}
//			rstab.close();
			String sCatalogName = conn.getCatalog();

			logger.debug("catalog name is:" + sCatalogName);

			logger.debug("got connection ok");

			Statement stmt = conn.createStatement();
			String sQuery =

			"select media.id as recid, media.full_filepath, mdint.id, mdesc.identifier, mdint.value, "
					+ " media.filename_search_index "
					+ "from media_table as media "
					+ " join media_to_metadata_table as mm on media.id = mm.media_id "
					+ " join metadata_integer_table as mdint on mm.metadata_id = mdint.id "
					+ " join metadata_description_table as mdesc on mdint.description_id = mdesc.id "
					+ "where mdesc.identifier = 'xmp:Rating' "
					+ "  and mime_type = 'image/jpeg'";

			PreparedStatement ps = conn.prepareStatement(sQuery);
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(sQuery);

			while (rs.next()) {

				int iImageId = rs.getInt("recid");
				String sPath = rs.getString("full_filepath");
				String sName = rs.getString("filename_search_index");

				ModelElement el = new ModelElement(sName);
				el.setId(iImageId);
				el.setFilename(sPath);

				a.add(el);

				logger.debug("fImageId:" + iImageId + sPath);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		r = a.toArray();
		return r;
	}

	public ModelMetaData[] getMetaData(int iId) {

		logger.debug("getMedaData start - id:" + iId);

		Object[] r = null;

		ArrayList<ModelMetaData> a = new ArrayList<ModelMetaData>();

		addMetaDataString(iId, a);
		addMetaDataInteger(iId, a);
		addMetaDataDecimal(iId, a);
		addMetaDataDateTime(iId, a);

		logger.debug("getMetaData stop - result is:" + a);
		return a.toArray(new ModelMetaData[a.size()]);
	}

	private void addMetaDataString(int iMediaId, ArrayList<ModelMetaData> a) {
		String sQuery =

		"select media.id, "
				+ "       mdesc.identifier, mdstr.value "
				+ "from media_table as media  "
				+ "  join media_to_metadata_table    as mm on media.id = mm.media_id "
				+ "  join metadata_string_table     as mdstr on mm.metadata_id = mdstr.id "
				+ "  join metadata_description_table as mdesc on mdstr.description_id = mdesc.id "
				+ "  where media.id = ?";

		PreparedStatement ps;
		try {
			ps = conn.prepareStatement(sQuery);
			ps.setInt(1, iMediaId);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {

				int iImageId = rs.getInt("id");
				String sName = rs.getString("identifier");
				String sValue = rs.getString("value");

				ModelMetaData meta = new ModelMetaData();
				meta.setName(sName);
				meta.setDataType("string");
				meta.setValue(sValue);

				a.add(meta);

				logger.debug("meta string:" + iImageId + " name:" + sName);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	private void addMetaDataInteger(int iMediaId, ArrayList<ModelMetaData> a) {
		String sQuery =

		"select media.id, "
				+ "       mdesc.identifier, mdint.value "
				+ "from media_table as media  "
				+ "  join media_to_metadata_table    as mm on media.id = mm.media_id "
				+ "  join metadata_integer_table         as mdint on mm.metadata_id = mdint.id "
				+ "  join metadata_description_table as mdesc on mdint.description_id = mdesc.id "
				+ "  where media.id = ?";

		try {
			PreparedStatement ps = conn.prepareStatement(sQuery);
			ps.setInt(1, iMediaId);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {

				int iImageId = rs.getInt("id");
				String sName = rs.getString("identifier");
				int iValue = rs.getInt("value");

				ModelMetaData meta = new ModelMetaData();
				meta.setName(sName);
				meta.setDataType("integer");
				meta.setValue(String.valueOf(iValue));

				a.add(meta);

				logger.debug("meta integer:" + iImageId + " name:"
						+ sName);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private void addMetaDataDecimal(int iMediaId, ArrayList<ModelMetaData> a) {
		try {


			String sQuery =

			"select media.id, "
					+ "       mdesc.identifier, mddec.value "
					+ "from media_table as media  "
					+ "  join media_to_metadata_table    as mm on media.id = mm.media_id "
					+ "  join metadata_decimal_table     as mddec on mm.metadata_id = mddec.id "
					+ "  join metadata_description_table as mdesc on mddec.description_id = mdesc.id "
					+ "  where media.id = ?";

			PreparedStatement ps = conn.prepareStatement(sQuery);
			ps.setInt(1, iMediaId);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {

				int iImageId = rs.getInt("id");
				String sName = rs.getString("identifier");
				// Timestamp tValue = rs.getTimestamp("value");
				String sValue = rs.getString("value");
				logger.debug("decimal is:" + sValue);

				ModelMetaData meta = new ModelMetaData();
				meta.setName(sName);
				meta.setDataType("decimal");
				// meta.setValue(tValue.toString());
				meta.setValue(sValue);

				a.add(meta);

				logger.debug("meta decimal:" + iImageId + " name:"
						+ sName);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	private void addMetaDataDateTime(int iMediaId, ArrayList<ModelMetaData> a) {

		String sQuery =

		"select media.id, "
				+ "       mdesc.identifier, mddt.value "
				+ "from media_table as media  "
				+ "  join media_to_metadata_table    as mm on media.id = mm.media_id "
				+ "  join metadata_date_time_table   as mddt on mm.metadata_id = mddt.id "
				+ "  join metadata_description_table as mdesc on mddt.description_id = mdesc.id "
				+ "  where media.id = ?";

		try {
			PreparedStatement ps = conn.prepareStatement(sQuery);
			ps.setInt(1, iMediaId);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {

				int iImageId = rs.getInt("id");
				String sName = rs.getString("identifier");
				// Timestamp tValue = rs.getTimestamp("value");
				String sValue = rs.getString("value");
				logger.debug("timestamp is:" + sValue);

				ModelMetaData meta = new ModelMetaData();
				meta.setName(sName);
				meta.setDataType("datetime");
				// meta.setValue(tValue.toString());
				meta.setValue(sValue);

				a.add(meta);

				logger.debug("meta date_time:" + iImageId + " name:"
						+ sName);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public ModelTag getTagTree() {

		logger.debug("getTagTree start");

		HashMap<Integer, ModelTag> map = new HashMap<Integer, ModelTag>();

		ArrayList<ModelTag> a = new ArrayList<ModelTag>();
		try {

			String sQuery =

			"select id, name, parent_id, type_name" + " from tag_table "
					+ " order by 1";

			PreparedStatement ps = conn.prepareStatement(sQuery);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {

				int iTagId = rs.getInt("id");
				String sTagName = rs.getString("name");
				String sTagType = rs.getString("type_name");
				int iTagParent = rs.getInt("parent_id");

				ModelTag tag = new ModelTag(iTagId, sTagName);
				tag.setParentId(iTagParent);
				tag.setType(sTagType);

				map.put(iTagId, tag);

				logger.debug("tag:" + tag);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		ModelTag root = new ModelTag(0, "root");
		map.put(0, root);

		for (ModelTag t : map.values()) {
			ModelTag locn = map.get(t.getTagId());
			if (locn != null) {
				if (locn.getTagId() != 0) {
					ModelTag parent = map.get(t.getParentId());
					parent.add(locn);
				}
			}
			logger.debug("tag is:" + t);
		}
		logger.debug("getTag stop - result is:" + a);

		root.printTree(root);

		root.applySubTree(root);
		
		root.printTree(root);
		
		return root;
	}

	public ModelTag[] getTags(int iId) {

		logger.debug("getTags start - id:" + iId);

		Object[] r = null;

		ArrayList<ModelTag> a = new ArrayList<ModelTag>();
		try {

			String sQuery =

			"select tt.id, tt.name "
					+ "from media_table as media "
					+ " join tag_to_media_table as t2m on media.id  = t2m.media_id "
					+ " join tag_table          as tt on t2m.tag_id = tt.id "
					+ "where media.id = ?" + " and  type_name like 'user%' ";

			PreparedStatement ps = conn.prepareStatement(sQuery);
			ps.setInt(1, iId);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {

				int iTagId = rs.getInt("id");
				String sTagName = rs.getString("name");

				ModelTag tag = new ModelTag(iTagId, sTagName);

				a.add(tag);

				logger.debug("tag:" + tag);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		logger.debug("getTag stop - result is:" + a);
		return a.toArray(new ModelTag[a.size()]);
	}

	private static void printCols(DatabaseMetaData dbmd, String schema,
			String table) {
		ResultSet rscol;
		try {
			rscol = dbmd.getColumns(null, schema, table, null);
			if (rscol != null) {
				while (rscol.next()) {
					String sColumn = rscol.getString("COLUMN_NAME");

					logger.debug("  column = " + sColumn);

				}
				rscol.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

}
